Declare @StartDate date
Declare @EndDate date

DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

SET @StartDate = '01-Jan-2014'
SET @EndDate = '01-Dec-2014'

declare @tmpTable as table
(
	TableNo int,
	TableDesc varchar(255)
)

Declare @strMonth varchar(3)

WHILE @StartDate < @EndDate
BEGIN
	SET @strMonth = left(datename(month,@StartDate),3)
	SET @SQLString = N'ALTER TABLE @tmpTable ADD @monthName varchar(3)'
	SET @ParmDefinition = N'@monthName varchar(3)'
	EXECUTE sp_executesql @SQLString, @ParmDefinition, @tmpTable = @tmpTable, @monthName = @strMonth
	SET @StartDate = DateAdd(month, 1, @StartDate)
END

Select * from @tmpTable

EXECUTE sp_executesql 'Select top 10 * from tblEntityTypeInfo', null

DECLARE @IntVariable int;
--DECLARE @SQLString nvarchar(500);
--DECLARE @ParmDefinition nvarchar(500);
DECLARE @max_title varchar(30);

SET @IntVariable = 197;
SET @SQLString = N'SELECT @max_titleOUT = max(JobTitle) 
   FROM AdventureWorks2012.HumanResources.Employee
   WHERE BusinessEntityID = @level';
SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
SELECT @max_title;



select top 10 * From tblCaseProfileIndex
Where Convert(varchar(8), ClosedOn, 112) = '20140531'

select * from tblPublicHolidays

select * from tblAskACRAEntityTypeInfo
select * from tblCaseSubjectInfo

select * from tblMonthlyDashboard Where Period = '01-Jul-2014'


--Update tblMonthlyDashboard SEt NInfo3 = (NInfo2/NInfo1) * 100
--WHERE TableNo IN (1,2,3,4,5,6,8,9,91,92, 13, 131, 132)
--And NInfo1 > 0 And NInfo2 > 0


--Update tblMonthlyDashboard SET NInfo4 = NULL
--Where TableNo IN (13,131,132)



select DateDiff(day, '01-Jan-2014', GetDate())
select DateAdd(day, -137, GetDate())



SELECT *
FROM (
    SELECT 
        TableNo, left(datename(month,Period),3)as [month], 
		NInfo3 as TotalCount 
    FROM tblMonthlyDashboard Where Period Between '01-Dec-2013' And GetDate()
) as s
PIVOT
(
    SUM(TotalCount)
    FOR [month] IN (dec,jan, feb, mar, apr, 
    may, jun, jul, aug, sep, oct, nov)
)AS [pivot]


	SELECT CASE 
			WHEN [2014-Jan] < 90 THEN 'Red' 
			ELSE 'Blue'
			END, [2014-Jan]
	FROM (
		SELECT 
			TableNo, CAST(year(Period) AS varchar(4)) + '-' + left(datename(month,Period),3) as [month], 
			NInfo3 as TotalCount 
		FROM tblMonthlyDashboard 
		Where Period Between '01-dec-2013' And GetDate()
		AND TableNo = 1
	) as s
	PIVOT
	(
		SUM(TotalCount)
		FOR [month] IN ([2013-Dec], [2014-Jan], [2014-Feb], [2014-Mar], [2014-Apr], 
		[2014-May], [2014-Jun], [2014-Jul])
	)AS [pivot]




                        
                        SELECT *
                        FROM (
                            SELECT 
                                TableNo, left(datename(month,Period),3)as [month], 
		                        NInfo3 as TotalCount 
                            FROM tblMonthlyDashboard Where Period Between '01-Jan-2014' And '01-Jul-2014') as s
                        PIVOT
                        (
                            SUM(TotalCount)
                            FOR [month] IN (Jan,Feb,Mar,Apr,May,Jun,Jul)
							) as [pivot]